package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vcp.entity.Site;
import vcp.entity.SiteCostUpdates;
import vcp.enums.SiteEnum;
import vcp.enums.SiteEnum.SiteCostsUpdateStatus;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.SiteCostsUpdateTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;
/**
 * <p> site costs updates DAO class implements all relevant sql queries </p>
 * @author VCP team
 *
 */
public class SiteCostUpdatesDAO implements IGenericDAO <SiteCostUpdates, Site>
{
	/****************************************************************************************************************************
	 *** Public methods ***
	 ****************************************************************************************************************************/
	/**
	 * updates the status for changing rates request 
	 * @param ID
	 * @param status
	 * @return true if success
	 */
	public boolean updateCostsStatus ( int ID, SiteCostsUpdateStatus status )
	{
		String sqlQuery =
				String.format(
						SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
						, TableNames.SITE_COSTS_UPDATE
						, SiteCostsUpdateTable.UPDATE_STATUS + "=" + "\"" + status.toString() + "\""
						, SiteCostsUpdateTable.ID + "=" + String.valueOf(ID));

		boolean success = this.refresh(sqlQuery);
		return success;
	}
	/**
	 * update is history row for given id
	 * @param ID
	 * @param isHistory
	 * @return true if success
	 */
	public boolean updateIsHistory ( int ID, boolean isHistory )
	{
		String sqlQuery =
				String.format(
						SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
						, TableNames.SITE_COSTS_UPDATE
						, SiteCostsUpdateTable.IS_HISTORY + "=" + isHistory
						, SiteCostsUpdateTable.ID + "=" + String.valueOf(ID));

		boolean success = this.refresh(sqlQuery);
		return success;
	}
	/**
	 * finds all the pending update prices for site
	 * @return list with site cost updates
	 */
	public ArrayList <SiteCostUpdates> findAllPendingSiteCostUpdates ()
	{
		ArrayList <SiteCostUpdates> siteCostUpdates = new ArrayList <>();
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.SITE_COSTS_UPDATE
						, SiteCostsUpdateTable.UPDATE_STATUS + "=" + "\"" + SiteCostsUpdateStatus.PENDING.toString() + "\"");

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				SiteCostUpdates costUpdate = this.create();

				costUpdate.setID(set.getInt(SiteCostsUpdateTable.ID));
				costUpdate.setSiteEnum(SiteEnum.convert(set.getString(SiteCostsUpdateTable.SITE_NAME)));
				costUpdate.setSingleUsePricePerHour(set.getFloat(SiteCostsUpdateTable.SINGLE_USE_HOUR));
				costUpdate.setOrderAheadPricePerHour(set.getFloat(SiteCostsUpdateTable.ORDER_AHEAD_HOUR));
				costUpdate.setCasualPriceSingleCar(set.getFloat(SiteCostsUpdateTable.CASUAL_PRICE));
				costUpdate.setFullPriceSingleCar(set.getFloat(SiteCostsUpdateTable.FULL_PRICE));
				costUpdate.setStatus(SiteCostsUpdateStatus.convert(set.getString(SiteCostsUpdateTable.UPDATE_STATUS)));
				costUpdate.setHistory(set.getBoolean(SiteCostsUpdateTable.IS_HISTORY));

				siteCostUpdates.add(costUpdate);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return siteCostUpdates;
	}
	/**
	 * find all non finished requests for cost update
	 * @param siteEnum
	 * @return list of cost updates
	 */
	public ArrayList <SiteCostUpdates> findAllNonFinishedSiteCostUpdates ( SiteEnum siteEnum )
	{
		ArrayList <SiteCostUpdates> siteCostUpdates = new ArrayList <>();
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.SITE_COSTS_UPDATE
						, SiteCostsUpdateTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\"" + " " + SQLQueries.AND + SiteCostsUpdateTable.IS_HISTORY + "=" + 0);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				SiteCostUpdates costUpdate = this.create();

				costUpdate.setID(set.getInt(SiteCostsUpdateTable.ID));
				costUpdate.setSiteEnum(SiteEnum.convert(set.getString(SiteCostsUpdateTable.SITE_NAME)));
				costUpdate.setSingleUsePricePerHour(set.getFloat(SiteCostsUpdateTable.SINGLE_USE_HOUR));
				costUpdate.setOrderAheadPricePerHour(set.getFloat(SiteCostsUpdateTable.ORDER_AHEAD_HOUR));
				costUpdate.setCasualPriceSingleCar(set.getFloat(SiteCostsUpdateTable.CASUAL_PRICE));
				costUpdate.setFullPriceSingleCar(set.getFloat(SiteCostsUpdateTable.FULL_PRICE));
				costUpdate.setStatus(SiteCostsUpdateStatus.convert(set.getString(SiteCostsUpdateTable.UPDATE_STATUS)));
				costUpdate.setHistory(set.getBoolean(SiteCostsUpdateTable.IS_HISTORY));

				siteCostUpdates.add(costUpdate);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return siteCostUpdates;
	}

	/****************************************************************************************************************************
	 *** IGenericDAO methods ***
	 ****************************************************************************************************************************/
	@Override
	public Integer findMaxID ()
	{
		Integer siteCostsID = null;
		String query =
				String.format(
						SQLQueries.SELECT_MAX__VALUE__AS__NAME__FROM__TABLE_NAME__
						, SiteCostsUpdateTable.ID
						, SQLQueries.MAX_ID
						, TableNames.SITE_COSTS_UPDATE);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				siteCostsID = set.getInt("maxID");
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		if ( siteCostsID == null )
		{
			siteCostsID = 0;
		}

		return ++ siteCostsID;
	}

	@Override
	public SiteCostUpdates create ()
	{
		return new SiteCostUpdates();
	}

	/**
	 * Not implemented
	 */
	@Override
	public SiteCostUpdates findByEntity ( Site entity )
	{
		return null;
	}

	@Override
	public SiteCostUpdates findByID ( int ID )
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public SiteCostUpdates findByID ( String ID )
	{
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.SITE_COSTS_UPDATE
						, SiteCostsUpdateTable.ID + "=" + ID);

		SiteCostUpdates siteCostUpates = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				siteCostUpates = this.create();

				siteCostUpates.setID(set.getInt(SiteCostsUpdateTable.ID));
				siteCostUpates.setSiteEnum(SiteEnum.convert(set.getString(SiteCostsUpdateTable.SITE_NAME)));
				siteCostUpates.setSingleUsePricePerHour(set.getFloat(SiteCostsUpdateTable.SINGLE_USE_HOUR));
				siteCostUpates.setOrderAheadPricePerHour(set.getFloat(SiteCostsUpdateTable.ORDER_AHEAD_HOUR));
				siteCostUpates.setCasualPriceSingleCar(set.getFloat(SiteCostsUpdateTable.CASUAL_PRICE));
				siteCostUpates.setFullPriceSingleCar(set.getFloat(SiteCostsUpdateTable.FULL_PRICE));
				siteCostUpates.setStatus(SiteCostsUpdateStatus.convert(set.getString(SiteCostsUpdateTable.UPDATE_STATUS)));
				siteCostUpates.setHistory(set.getBoolean(SiteCostsUpdateTable.IS_HISTORY));
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return siteCostUpates;
	}

	@Override
	public List <SiteCostUpdates> findAll ( Site info )
	{
		List <SiteCostUpdates> siteCostUpdates = new ArrayList <>();
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.SITE_COSTS_UPDATE
						, SiteCostsUpdateTable.SITE_NAME + "=" + "\"" + info.getSiteName() + "\"");

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				SiteCostUpdates costUpdate = this.create();

				costUpdate.setID(set.getInt(SiteCostsUpdateTable.ID));
				costUpdate.setSiteEnum(SiteEnum.convert(set.getString(SiteCostsUpdateTable.SITE_NAME)));
				costUpdate.setSingleUsePricePerHour(set.getFloat(SiteCostsUpdateTable.SINGLE_USE_HOUR));
				costUpdate.setOrderAheadPricePerHour(set.getFloat(SiteCostsUpdateTable.ORDER_AHEAD_HOUR));
				costUpdate.setCasualPriceSingleCar(set.getFloat(SiteCostsUpdateTable.CASUAL_PRICE));
				costUpdate.setFullPriceSingleCar(set.getFloat(SiteCostsUpdateTable.FULL_PRICE));
				costUpdate.setStatus(SiteCostsUpdateStatus.convert(set.getString(SiteCostsUpdateTable.UPDATE_STATUS)));
				costUpdate.setHistory(set.getBoolean(SiteCostsUpdateTable.IS_HISTORY));

				siteCostUpdates.add(costUpdate);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return siteCostUpdates;
	}

	@Override
	public boolean save ( SiteCostUpdates entity )
	{
		boolean result = false;
		String valuesSet =
				String.format(
						"%d, \"%s\", %f, %f, %f, %f,\"%s\",%d"
						, entity.getID()
						, entity.getSiteName()
						, entity.getSingleUsePricePerHour()
						, entity.getOrderAheadPricePerHour()
						, entity.getCasualPriceSingleCar()
						, entity.getFullPriceSingleCar()
						, entity.getStatus().toString()
						, entity.isHistory()
						? 1
								: 0);

		String sqlQuery =
				String.format(
						SQLQueries.INSERT_INTO__TABLENAME__VALUES__
						, TableNames.SITE_COSTS_UPDATE
						, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh ( String sqlQuery )
	{
		boolean success = false;

		try
		{
			success = DBAdapter.getAdapter().refresh(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return success;
	}

	@Override
	public boolean deleteByID ( int ID )
	{
		return this.deleteByID(String.valueOf(ID));
	}

	@Override
	public boolean deleteByID ( String ID )
	{
		String sqlQuery =
				String.format(
						SQLQueries.DELETE_FROM__TABLENAME__WHERE__COMPARE_
						, TableNames.SITE_COSTS_UPDATE
						, SiteCostsUpdateTable.ID + "=" + ID);

		boolean success = false;
		try
		{
			success = DBAdapter.getAdapter().delete(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return success;
	}

}
